package sk.fiit.vppj.eventmanager.gui.judge;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import sk.fiit.vppj.eventmanager.core.DatabaseManager;
import sk.fiit.vppj.eventmanager.core.UserManager;
import sk.fiit.vppj.eventmanager.data.ProjectAdminEval;
import sk.fiit.vppj.eventmanager.data.ProjectSingleEval;
/**
 * Class distributes evaluations of projeects to the whole appliacation
 * @author Marek
 *
 */
public final class Evaluation {
	public Evaluation() {
	}

	/**
	 * Method saves evaluation into database. In case of new record it uses INSERT, if record already exists UPDATE is used
	 * @param eval_id - Id of evaluation
	 * @param event_id - Id of current event
	 * @param project_id - Id of project
	 * @param user_id - Id of user who evaluates
	 * @param criteria_id - Criterium which is evaluated
	 * @param points - How many points you gives
	 */
	public void saveEvaluation(int eval_id, int event_id, int project_id, int user_id, int criteria_id, int points) {
		String sql = "INSERT INTO jos_event_manager_project_evaluation (IDProject_evaluation, event_id,"
				+ " project_id, user_id, criteria_id, points ) VALUES ( ?,?,?,?,?,? ) ON DUPLICATE KEY UPDATE points = ?";
		int types[] = { java.sql.Types.INTEGER, java.sql.Types.INTEGER, java.sql.Types.INTEGER, java.sql.Types.INTEGER,
				java.sql.Types.INTEGER, java.sql.Types.DOUBLE, java.sql.Types.DOUBLE };

		DatabaseManager.getInstance().getJdbcTemplate()
				.update( sql, new Object[] { eval_id, event_id, project_id, user_id, criteria_id, ( double ) points, ( double ) points },
						types );
	}

	/**
	 * Method returns evaluations of selected project made by selected user
	 * @param projectId - Id of project, you want to see
	 * @param user_id - Id of user, who made the evaluations
	 * @return List of evaluations, each criterium is single object
	 */
	public List<ProjectSingleEval> getSingleEvaluations(int projectId, int user_id) {

		return DatabaseManager.getInstance().getJdbcTemplate()
				.query( prepareStatementSingleUser( projectId, user_id ), new RowMapper<ProjectSingleEval>() {

					public ProjectSingleEval mapRow(ResultSet rs, int rowNum) throws SQLException {
						ProjectSingleEval tempP = new ProjectSingleEval();
						tempP.setEvalId( rs.getInt( "IDProject_evaluation" ) );
						tempP.setCriteriaId( rs.getInt( "IDProject_criteria" ) );
						tempP.setCriteriaName( rs.getString( "criteriaName" ) );
						tempP.setPoints( rs.getInt( "points" ) );
						tempP.setMaxPoints( rs.getInt( "total_points" ) );

						return tempP;
					}
				} );
	}

	/**
	 * Method which return all evaluations from any judge to selected project
	 * @param projectId - Id of project to which you want to see all evaluations
	 * @return
	 */
	public List<ProjectAdminEval> getAdminEvaluations(int projectId) {
		final String sql = "SELECT c.name AS criteriaName, c.total_points, "
				+ " c.IDProject_criteria, "
				+ " e.points, jos_users.name AS judgeName FROM jos_event_manager_project_criteria AS c LEFT JOIN jos_event_manager_project_evaluation AS e "
				+ " ON ( c.IDProject_criteria = e.criteria_id  AND ( e.project_id = ? OR e.project_id IS NULL ) ) "
				+ " LEFT JOIN jos_users ON jos_users.id = e.user_id WHERE " + " c.event_id = ? ORDER BY e.user_id, c.IDProject_criteria ASC ";

		List<Map<String, Object>> temp = DatabaseManager.getInstance().getJdbcTemplate()
				.queryForList( sql, new Object[] { projectId, UserManager.getInstance().getUser().getEvent_id() } );

		List<ProjectAdminEval> data = new ArrayList<ProjectAdminEval>( temp.size() );
		ProjectAdminEval t = null;
		for ( Map<String, Object> x : temp ) {
			t = new ProjectAdminEval();
			t.setData( x );
			data.add( t );
		}
		return data;
	}

	/**
	 * Prepares statement which returns result set which suits ProjectAdminEval object 
	 * @param projectId
	 * @param user_id
	 * @return
	 */
	private PreparedStatementCreator prepareStatementSingleUser(final int projectId, final int user_id) {
		final String sql = "SELECT c.name AS criteriaName, c.total_points, c.IDProject_criteria, e.IDProject_evaluation,"
				+ " e.points FROM jos_event_manager_project_criteria AS c LEFT JOIN jos_event_manager_project_evaluation AS e "
				+ " ON ( c.IDProject_criteria = e.criteria_id "
				+ " AND ( ( e.user_id = ? AND e.project_id = ? ) OR e.user_id IS NULL ) ) WHERE " + " c.event_id = ?";

		return new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, user_id );
				ps.setInt( 2, projectId );
				ps.setInt( 3, UserManager.getInstance().getUser().getEvent_id() );

				return ps;
			}
		};
	}
}
